<?php
/**
 * Created by PhpStorm.
 * User: admin
 * Date: 2016/9/22
 * Time: 9:47
 *订单管理
 */

date_default_timezone_set('PRC');
define('ENV', 'test');

switch (ENV) {
    case 'dev':
        define('SERVER', '127.0.0.1');
        define('PORT', '3307');
        define('DBNAME', 'jhdb');
        define('DBUSER', 'root');
        define('PWD', 'usbw');
        define('CHARSET', 'uft8');
        break;
    case 'test':
        define('SERVER', '127.0.0.1');
        define('PORT', '3306');
        define('DBNAME', 'jhdb');
        define('DBUSER', 'root');
        define('PWD', 'jh!@#2016');
        define('CHARSET', 'uft8');
        break;
    case 'prod':
        define('SERVER', '127.0.0.1');
        define('PORT', '3306');
        define('DBNAME', 'jhdb');
        define('DBUSER', 'root');
        define('PWD', 'jh!@#2016');
        define('CHARSET', 'uft8');
        break;
    default:
        break;
}

function dropTmpTable()
{
    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');


        $sql = "DROP TABLE `t_tmp_orders`;";


        $createDb = $pdo->exec($sql);
        return $createDb;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

function createTemTable()
{

    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');


        $sql = "CREATE  TABLE IF NOT EXISTS `t_tmp_orders` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `web_site` VARCHAR(100) NOT NULL COMMENT '服务器站点',
  `order_sn` VARCHAR(50) NOT NULL COMMENT '订单号',
  `userid` VARCHAR(255) NOT NULL COMMENT '用户cookieID',
  `shipping_method` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '配送方式',
  `pay_type` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '付款方式',
  `user_name` VARCHAR(255) DEFAULT NULL COMMENT '收件人名稱',
  `shipping_time` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '收貨時間',
  `phone` VARCHAR(255) DEFAULT NULL COMMENT '聯絡電話',
  `line` VARCHAR(255) DEFAULT NULL COMMENT 'FB号码',
  `addres` VARCHAR(2000) DEFAULT NULL COMMENT '詳細地址',
  `gender` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '性別',
  `email` VARCHAR(255) DEFAULT NULL COMMENT '郵箱',
  `pay_info` text COMMENT '支付信息',
  `note` TEXT COMMENT '備註',
  `status` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '订单状态 0未完成  1完成',
  `createtime` INT(11) NOT NULL COMMENT '订单生成时间',
  `modifytime` INT(11) NOT NULL COMMENT '订单修改时间',
  PRIMARY KEY (`id`),
 UNIQUE KEY `web_order` (`web_site`,`order_sn`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='订单记录表';";


        $createDb = $pdo->exec($sql);
        return $createDb;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


//查出所有库的所有订单数据
function getAllData()
{
    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');

        $dbs = $pdo->query("select * from dbs_config")->fetchAll();
        foreach ($dbs as $v) {
            $dbcontion_server = new PDO('mysql:dbname=' . $v["dbname"] . ';host=' . $v["host"] . ';port=' . $v["port"], $v["user"], $v["pwd"]);
            $dbcontion_server->query("set names utf8");
            $sql = "select * from jh_order_sn_record ";
            $ret = $dbcontion_server->query($sql)->fetchAll();
            $allres[$v["website"]] = $ret;
        }

        return $allres;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

//将查出的数据导入临时表中
function dumpDataToTmp($sql)
{

    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');


        $ret = $pdo->exec($sql);
        return $ret;
    } catch (PDOException $e) {
        echo $e->getMessage();
        exit;
    }
}


function getOrdersBySearch($search)
{
    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');


        $allresults = $pdo->query("select * from `jh_order_sn_record` $search ")->fetchAll();

        return $allresults;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


//根据每个网站来查购物车商品
function getShopCartBySn($sn, $web="")
{
    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');


        $sql = "select * from jh_shop_cart where order_sn = '$sn'";
        $ret = $pdo->query($sql)->fetchAll();
        return $ret;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

//根据每个网站的每個訂單來查詢快遞發貨情況
function getKdInfoBySn($sn, $web="")
{
    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');


        $sql = "select * from jh_logistics where order_sn = '$sn'";
        $ret = $pdo->query($sql)->fetch();
        return $ret;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

//根据网站来查相应的产品
function getProductById($product_id, $web="")
{
    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');


        $sql = "select * from products where products_id = '$product_id'";
        $ret = $pdo->query($sql)->fetch();
        return $ret;

    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


function getBlackNameList($type)
{
    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');

        if ($type == 'user_name') {
            $all = $pdo->query("select user_name from black_list")->fetchAll();
        } elseif ($type == 'phone') {
            $all = $pdo->query("select phone from black_list")->fetchAll();
        } elseif ($type == 'line') {
            $all = $pdo->query("select line from black_list")->fetchAll();
        } else {
            $all = $pdo->query("select email from black_list")->fetchAll();
        }

        return $all;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


function getValueByKey($key, $filename)
{
    $rt = '';
    $csvArray = getCsv($filename);
    foreach ($csvArray as $k => $v) {
        if ($key == $v[0]) {
            $rt = $v[1];
            break;
        }
    }

    return $rt;
}

function getCsv($filename)
{
    setlocale(LC_ALL, 'zh_CN');
    $path = $filename;
    $file = fopen($path, 'r');
    while ($data = fgetcsv($file)) { //每次读取CSV里面的一行内容
        $lists[] = $data;
    }

    return $lists;
    fclose($file);
}


function getLogByOrderSn($sn)
{
    try {
        $pdo = null;
        $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
        $pdo->setAttribute(PDO::ATTR_PERSISTENT, true);
        $pdo->exec('SET NAMES utf8');


        $all = $pdo->query("select * from payment_log WHERE OrderID = '$sn'")->fetch();


        return $all;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}


header('Content-Type: text/html;charset=utf-8');
$start_date = $_GET['start_date'];
$end_date = $_GET['end_date'];
$user_name = $_GET['user_name'];
$phone = $_GET['phone'];

if ($start_date == '' || $end_date == '') {
    echo '开始时间或者结束时间都不能为空';
    exit;
}

$start_time = strtotime($start_date);
$end_time = strtotime($end_date) + (23 * 3600) + (59 * 60) + 59;

if ($start_time > $end_time) {
    echo '开始时间不能小于结束时间';
    exit;
}

if (($end_time - $start_time) > 30 * 24 * 3600) {
    echo '查询周期不能操作30天';
    exit;
}


$search_str = " WHERE `status` = 0 and createtime >= '$start_time' and createtime <= '$end_time'";
if ($phone != '' && $user_name == '') {
    $search_str .= " AND phone = '$phone'";
} elseif ($phone == '' && $user_name != '') {
    $search_str .= " AND user_name = '$user_name'";
} elseif ($phone != '' && $user_name != '') {
    $search_str .= " AND user_name = '$user_name' AND phone = '$phone'";
}
//try {
//    $pdo = new PDO('mysql:host=' . SERVER . ';port=' . PORT . ';dbname=' . DBNAME, DBUSER, PWD);
//    $pdo->exec('SET NAMES \'' . CHARSET . '\'');
//} catch (Exception $e) {
//    $e->getMessage();
//}
$new_orders = array();

//$sql = "SELECT * FROM  jh_order_sn_record" . $search_str . " ORDER BY createtime DESC;";
//$pdo->exec("set names utf8");
//$query = $pdo->query($sql);
//$orders = $query->fetchAll(PDO::FETCH_ASSOC);


//先创建临时表 将数据导入 然后在再临时表中进行查询等操作
//$ret_drop = dropTmpTable();
//$db = createTemTable();
//$allData = getAllData();
//$sql_str = '';
//$sign = ',';
//foreach ($allData as $website => $data) {
//    if (!$data) {
//        unset($allData[$website]);
//    }
//}
//
//foreach ($allData as $website => $data) {
//    if ($data) {
//        foreach ($data as $item) {
//
//            $ws = "'" . $website . "'";
//            $order_sn = "'" . $item['order_sn'] . "'";
//            $userid = "'" . $item['userid'] . "'";
//            $shipping_method = "'" . $item['shipping_method'] . "'";
//            $pay_type = "'" . $item['pay_type'] . "'";
//            $user_name = "'" . $item['user_name'] . "'";
//            $shipping_time = "'" . $item['shipping_time'] . "'";
//            $phone = "'" . $item['phone'] . "'";
//            $line = "'" . $item['line'] . "'";
//            $addres = "'" . $item['addres'] . "'";
//            $gender = "'" . $item['gender'] . "'";
//            $email = "'" . $item['email'] . "'";
//            $pay_info = "'" . $item['pay_info'] . "'";
//            $note = "'" . $item['note'] . "'";
//            $status = "'" . $item['status'] . "'";
//            $createtime = "'" . $item['createtime'] . "'";
//            $modifytime = "'" . $item['modifytime'] . "'";
//            $value_str_s = '(' . $ws . ',' . $order_sn . ',' . $userid . ',' . $shipping_method . ',' . $pay_type . ',' . $user_name . ',' . $shipping_time . ',' . $phone . ',' . $line . ',' . $addres . ',' . $gender . ',' . $email . ',' . $pay_info . ',' . $note . ',' . $status . ',' . $createtime . ',' . $modifytime . ')';
//            $sql_str .= $value_str_s . $sign;
//        }
//    }
//}
//
//
//$sql_str = substr($sql_str, 0, strlen($sql_str) - 1);
//
//$sql_exec_str = "replace into `t_tmp_orders` (`web_site`,`order_sn`,`userid`,`shipping_method`,`pay_type`,`user_name`,`shipping_time`,`phone`,`line`,`addres`,`gender`,`email`,`pay_info`,`note`,`status`,`createtime`,`modifytime`) values $sql_str";
//
//
//$dumpRet = dumpDataToTmp($sql_exec_str);

$orders = getOrdersBySearch($search_str);

//$orders = AdminOrder::getOrdersBySearchAll($search_str);
foreach ($orders as $ord) {
    $sn = $ord['order_sn'];
//    $sql_shop = "SELECT * FROM  jh_shop_cart WHERE order_sn = '$sn';";
//    $pdo->exec("set names utf8");
//    $query = $pdo->query($sql_shop);
//    $shop_cart = $query->fetchAll(PDO::FETCH_ASSOC);

    $shop_cart = getShopCartBySn($sn);
    foreach ($shop_cart as $sc) {
        $pid = $sc['product_id'];
//        $sql_prod = "SELECT * FROM  products WHERE products_id = '$pid';";
//        $pdo->exec("set names utf8");
//        $query1 = $pdo->query($sql_prod);
//        $product = $query1->fetch(PDO::FETCH_ASSOC);

        $product = getProductById($pid);


        $new_order['order_sn'] = $ord['order_sn'];
        $new_order['user_name'] = $ord['user_name'];
        $new_order['phone'] = $ord['phone'];
        $new_order['products_name'] = $product['products_name'];
        $new_order['num'] = $sc['product_nums'];
        $new_order['products_price'] = $product['products_special_price'];
        $new_order['total_price'] = $product['products_special_price'] * $new_order['num'];
        $new_order['products_model'] = $product['products_model'];
        $new_order['products_size'] = $sc['size'];
        $new_order['products_color'] = $sc['color'];


        $nameslist = getBlackNameList('user_name');
        $phonelist = getBlackNameList('phone');
        $linelist = getBlackNameList('line');
        $emaillist = getBlackNameList('email');

        if ($nameslist || $phonelist || $linelist || $emaillist) {
            foreach ($nameslist as $n) {
                $names[] = $n['user_name'];
            }

            foreach ($phonelist as $n) {
                $phones[] = $n['phone'];
            }

            foreach ($linelist as $n) {
                $lines[] = $n['line'];
            }

            foreach ($emaillist as $n) {
                $emails[] = $n['email'];
            }


            if (($names && in_array($ord['user_name'], $names)) || ($phones && in_array($ord['phone'], $phones)) || ($lines && in_array($ord['line'], $lines)) || ($emails && in_array($ord['email'], $emails))) {
                $new_order['sadOrder'] = "疑似恶意订单";
            } else {
                $new_order['sadOrder'] = "正常订单";
            }
        } else {
            $new_order['sadOrder'] = "正常订单";
        }


        $new_order['pay_type'] = getValueByKey($ord['pay_type'], dirname(__FILE__) . '/../../../csv/pay_method.csv');
        $payLog = getLogByOrderSn($ord['order_sn']);
        if ($ord['pay_type'] == '111') {
            $new_order['pay_info'] = "貨到付款用戶";
        } else {
            if (!$payLog) {
                $new_order['pay_info'] = "支付未成功，漏單用戶";
            } else {
                if (!$payLog['RetStatus'] && !$payLog['RetCode']) {
                    $new_order['pay_info'] = "点击支付按钮之后关闭浏览器导致支付失败";
                } else {
                    if (!$payLog['RetStatus'] && $payLog['RetStatus'] == 1) {
                        $new_order['pay_info'] = "支付成功";
                    } else {
                        $new_order['pay_info'] = $payLog['RetMsg'];
                    }
                }
            }
        }


        $new_order['addres'] = $ord['addres'];
        $new_order['note'] = $ord['note'];
        $new_order['createtime'] = date("Y-m-d H:i:s", $ord['createtime']);


//        $sql_kd = "SELECT * FROM  jh_logistics WHERE order_sn = '$sn';";
//        $pdo->exec("set names utf8");
//        $query2 = $pdo->query($sql_kd);
        $kd = getKdInfoBySn($sn);
        if ($kd) {
//            $kd = $query2->fetch(PDO::FETCH_ASSOC);
//    $kd = AdminKd::getKdInfoBySn($ord['order_sn']);
            $new_order['kd_name'] = $kd['kd_name'];
            if ($kd['isGo'] == 1) {
                $new_order['kd_status'] = '已发货';
            } elseif ($kd['isGo'] == 0) {
                $new_order['kd_status'] = '未发货';
            } else {
                $new_order['kd_status'] = '取消订单';
            }
        } else {
            $new_order['kd_name'] = '无';
            $new_order['kd_status'] = '未发货';
        }
        $new_orders[] = $new_order;
    }
}

if (!$new_orders) {
    exit("无订单数据");
}

require_once dirname(__FILE__) . '/../include/lib/PHPExcelClasses/PHPExcel.php';
$fileName_s = "订单表";
$headArr_s = array(
    '订单号',
    '用户名称',
    '手机号',
    '产品名称',
    '购买数量',
    '产品单价',
    '产品总价',
    '产品型号',
    '产品尺码',
    '产品颜色',
    '订单状态',
    '付款方式',
    '付款信息',
    '送货地址',
    '留言备注',
    '下单时间',
    '快递名称',
    '当前快递状态');
$data_source = $new_orders;

getExcel($fileName_s, $headArr_s, $data_source);

function getExcel($fileName, $headArr, $data)
{
    if (empty($data) || !is_array($data)) {
        die("data must be a array");
    }
    if (empty($fileName)) {
        exit;
    }
    $date = date("Y_m_d", time());
    $fileName .= "_{$date}.xlsx";

    //创建新的PHPExcel对象
    $objPHPExcel = new PHPExcel();
    $objProps = $objPHPExcel->getProperties();

    //设置表头
    $key = ord("A");
    foreach ($headArr as $v) {
        $colum = chr($key);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
        $key += 1;
    }

    $column = 2;
    $objActSheet = $objPHPExcel->getActiveSheet();
    foreach ($data as $key => $rows) { //行写入
        $span = ord("A");
        foreach ($rows as $keyName => $value) {// 列写入
            $j = chr($span);
            $objActSheet->setCellValue($j . $column, $value);
            $span++;
        }
        $column++;
    }

    $fileName = iconv("utf-8", "gb2312", $fileName);
    //重命名表
    $objPHPExcel->getActiveSheet()->setTitle('Simple');
    //设置活动单指数到第一个表,所以Excel打开这是第一个表
    $objPHPExcel->setActiveSheetIndex(0);
    //将输出重定向到一个客户端web浏览器(Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header("Content-Disposition: attachment; filename=\"$fileName\"");
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

    $objWriter->save('php://output'); //文件通过浏览器下载

    exit;
}






//$objPHPExcel = new PHPExcel();
//$objPHPExcel->getProperties()
//    ->setTitle("Office 2007 XLSX Document")
//    ->setSubject("Office 2007 XLSX Document")
//    ->setDescription("document for Office 2007 XLSX, generated using PHP classes.")
//    ->setKeywords("office 2007 openxml php")
//    ->setCategory("result file");
//
//$objPHPExcel->setActiveSheetIndex(0)
//    ->setCellValue('A1', '订单号')
//    ->setCellValue('B1', '用户名称')
//    ->setCellValue('C1', '手机号')
//    ->setCellValue('D1', '产品名称')
//    ->setCellValue('E1', '购买数量')
//    ->setCellValue('F1', '产品单价')
//    ->setCellValue('G1', '产品总价')
//    ->setCellValue('H1', '产品型号')
//    ->setCellValue('I1', '产品尺码')
//    ->setCellValue('J1', '产品颜色')
//    ->setCellValue('K1', '送货地址')
//    ->setCellValue('L1', '留言备注')
//    ->setCellValue('M1', '下单时间')
//    ->setCellValue('N1', '快递名称')
//    ->setCellValue('O1', '当前快递状态');
//
//$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8);
//$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
//$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
//$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
//$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(22);
//$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
//$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
//$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
//$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
//$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
//$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
//$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
//$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
//$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(18);
//
//
//$k = 2;
//for ($i = 0; $i < count($new_orders); $i++) {
//    $row = $new_orders[$i];
//    $objPHPExcel->setActiveSheetIndex(0)
//        ->setCellValue("A" . $k, $row['order_sn'])
//        ->setCellValue("B" . $k, $row['user_name'])
//        ->setCellValue("C" . $k, $row['phone'])
//        ->setCellValue("D" . $k, $row['products_name'])
//        ->setCellValue("E" . $k, $row['num'])
//        ->setCellValue("F" . $k, $row["products_price"])
//        ->setCellValue("G" . $k, $row["total_price"])
//        ->setCellValue("H" . $k, $row['products_model'])
//        ->setCellValue("I" . $k, $row['products_size'])
//        ->setCellValue("J" . $k, $row['products_color'])
//        ->setCellValue("K" . $k, $row['addres'])
//        ->setCellValue("L" . $k, $row['note'])
//        ->setCellValue("M" . $k, $row['createtime'])
//        ->setCellValue("N" . $k, $row['kd_name'])
//        ->setCellValue("O" . $k, $row['kd_status']);
//}
//$objPHPExcel->getActiveSheet()->setTitle('订单表');
//
//
//header('Content-Type: application/vnd.ms-excel');
//header('Content-Disposition: attachment;filename="订单表.xls"');
//header('Cache-Control: max-age=0');
//header('Cache-Control: max-age=1');
//header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
//header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
//header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
//header('Pragma: public'); // HTTP/1.0
//
//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//$objWriter->save('php://output');
//exit;

